package vn.airlines.web;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class clsConnect {

    Connection con;
    String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String urlCon = "jdbc:sqlserver://localhost:1433;databaseName=";
    String databaseName = "ABC";
    String userName = "sa";
    String passWord = "123456";

    public clsConnect() {

        try {
            Class.forName(driver);
            con = DriverManager.getConnection(urlCon + databaseName, userName, passWord);
        } catch (Exception e) {
        }
    }

    public void disConnect() {
        try {
            con.close();
        } catch (SQLException ex) {
        }
    }

    public String callSql(String sql, int nParameter) {
        String ret = "{call " + sql + "(";
        for (int i = 0; i < nParameter; i++) {
            if (i == nParameter - 1) {
                ret += "?";
            } else {
                ret += "?,";
            }
        }
        ret += ")}";
        return ret;

    }

    public Object[][] loadData(String sql) {
        try {
            CallableStatement stm = con.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = stm.executeQuery();
            rs.last();
            ResultSetMetaData rsmd = rs.getMetaData();
            int countCol = rsmd.getColumnCount();
            int countRow = rs.getRow();
            Object[][] result = new Object[countRow][countCol];
            rs.first();
            for (int i = 0; i < countRow; i++) {
                for (int j = 0; j < countCol; j++) {
                    result[i][j] = rs.getObject(j + 1);
                }
                rs.next();
            }
            rs.close();
            stm.close();
            return result;

        } catch (Exception ex) {
        }
        return null;
    }

    /*
     name = "@dd" -> sai
     public Object[][] loadData(String sql, String[] name, Object[] value, int nParameter) {
     try {
     CallableStatement stm = con.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
     for (int i = 0; i < nParameter; i++) {
     stm.setObject(i+1, value[i]);
     }
     ResultSet rs = stm.executeQuery();
     rs.last();
     ResultSetMetaData rsmd = rs.getMetaData();
     int countCol = rsmd.getColumnCount();
     int countRow = rs.getRow();
     Object[][] result = new Object[countRow][countCol];
     rs.first();
     for (int i = 0; i < countRow; i++) {
     for (int j = 0; j < countCol; j++) {
     result[i][j] = rs.getObject(j + 1);
     }
     rs.next();
     }
     rs.close();
     stm.close();
     return result;
     } catch (Exception ex) {
     Object[][] result = new Object[1][1];
     result[0][0]= ex.toString();
     return result;
     }
     }*/
    public Object[][] loadData(String sql, String[] name, Object[] value, int nParameter) {
        try {
            CallableStatement stm = con.prepareCall(callSql(sql, nParameter), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < nParameter; i++) {
                stm.setObject(i + 1, value[i]);
            }
            ResultSet rs = stm.executeQuery();
            rs.last();
            ResultSetMetaData rsmd = rs.getMetaData();
            int countCol = rsmd.getColumnCount();
            int countRow = rs.getRow();
            Object[][] result = new Object[countRow][countCol];
            rs.first();
            for (int i = 0; i < countRow; i++) {
                for (int j = 0; j < countCol; j++) {
                    result[i][j] = rs.getObject(j + 1);
                }
                rs.next();
            }
            rs.close();
            stm.close();
            return result;
        } catch (Exception ex) {
            Object[][] result = new Object[1][1];
            result[0][0] = ex.toString();
            return result;
        }
    }

    public void executeQuery(String sql) {
        try {
            CallableStatement stm = con.prepareCall(sql);
            stm.executeQuery();
            stm.close();

        } catch (Exception ex) {
        }
    }

    public void executeQuery(String sql, Object[] value, int nParameter) {
        try {
            CallableStatement stm = con.prepareCall(callSql(sql, nParameter), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < nParameter; i++) {
                stm.setObject(i + 1, value[i]);
            }
            stm.executeQuery();
            stm.close();
        } catch (Exception ex) {
        }

    }

    public ResultSet excuteDataReader(String sql) {
        try {
            CallableStatement stm = con.prepareCall(sql);
            return stm.executeQuery();
        } catch (Exception ex) {
        }
        return null;
    }

    public ResultSet excuteDataReader(String sql, Object[] value, int nParameter) {
        try {
            CallableStatement stm = con.prepareCall(callSql(sql, nParameter), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < nParameter; i++) {
                stm.setObject(i + 1, value[i]);
            }
            return stm.executeQuery();
        } catch (Exception ex) {
            ex.toString();
            ex.toString();
        }
        return null;
    }
}
